"""实销实结底表字段更新程序
功能：将 dz_real_sales_detail 表中的字段更新到 dz_real_sales_bottomtable 表中对应字段
字段映射：
  - last_purchase_price -> last_purchase_price
  - goodsQty -> goodsQty
  - goodsQtyIn -> current_month_inventory
  - goodsQtyInAmt -> current_month_inventory_amount
关联条件：good_no（货品编码）
"""

import sys
import os
import logging
from typing import Tuple, Optional

# 添加项目根目录到 Python 路径
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', '..', '..'))
from config import DB

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


def update_bottomtable_fields() -> bool:
    """
    更新底表字段主函数
    
    Returns:
        bool: 更新成功返回True，失败返回False
    """
    logger.info("开始执行底表字段更新程序")

    conn = None
    cursor = None

    try:
        # 建立数据库连接
        conn = DB()
        cursor = conn.cursor()
        logger.info("数据库连接成功")

        # 执行字段更新
        success = _execute_field_update(cursor)

        if success:
            conn.commit()
            logger.info("字段更新完成并提交事务")
            return True
        else:
            conn.rollback()
            logger.error("字段更新失败，已回滚事务")
            return False

    except Exception as e:
        if conn:
            conn.rollback()
        logger.error(f"更新过程中发生异常: {str(e)}")
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        logger.info("数据库连接已关闭")


def _execute_field_update(cursor) -> bool:
    """
    执行字段更新操作
    
    Args:
        cursor: 数据库游标对象
        
    Returns:
        bool: 更新成功返回True，失败返回False
    """
    try:
        # 1. 首先检查数据匹配情况
        match_count = _check_data_match(cursor)
        if match_count == 0:
            logger.warning("没有找到可匹配的数据记录")
            return False

        logger.info(f"找到 {match_count} 条可匹配的数据记录")

        # 2. 执行字段更新
        update_sql = """
        UPDATE dz_real_sales_bottomtable AS bt
        INNER JOIN dz_real_sales_detail AS rd ON bt.goods_no = rd.goods_no
        SET 
            bt.last_purchase_price = CAST(rd.last_purchase_price AS CHAR),
            bt.goodsQty = CAST(rd.goodsQty AS CHAR),
            bt.current_month_inventory = CAST(rd.goodsQtyIn AS CHAR),
            bt.current_month_inventory_amount = CAST(rd.goodsQtyInAmt AS CHAR)
        WHERE 
            rd.last_purchase_price IS NOT NULL 
            OR rd.goodsQty IS NOT NULL
            OR rd.goodsQtyIn IS NOT NULL
            OR rd.goodsQtyInAmt IS NOT NULL
        """

        cursor.execute(update_sql)
        affected_rows = cursor.rowcount

        logger.info(f"成功更新 {affected_rows} 条记录")

        # 3. 验证更新结果
        _verify_update_result(cursor)

        return True

    except Exception as e:
        logger.error(f"执行字段更新时发生错误: {str(e)}")
        return False


def _check_data_match(cursor) -> int:
    """
    检查数据匹配情况
    
    Args:
        cursor: 数据库游标对象
        
    Returns:
        int: 匹配的记录数量
    """
    check_sql = """
    SELECT COUNT(*) as match_count
    FROM dz_real_sales_bottomtable AS bt
    INNER JOIN dz_real_sales_detail AS rd ON bt.goods_no = rd.goods_no
    WHERE rd.last_purchase_price IS NOT NULL 
       OR rd.goodsQty IS NOT NULL
       OR rd.goodsQtyIn IS NOT NULL
       OR rd.goodsQtyInAmt IS NOT NULL
    """

    cursor.execute(check_sql)
    result = cursor.fetchone()
    return result[0] if result else 0


def _verify_update_result(cursor) -> None:
    """
    验证更新结果
    
    Args:
        cursor: 数据库游标对象
    """
    verify_sql = """
    SELECT 
        bt.goods_no,
        bt.good_name,
        bt.last_purchase_price as bt_price,
        bt.goodsQty as bt_qty,
        bt.current_month_inventory as bt_inventory,
        bt.current_month_inventory_amount as bt_inventory_amt,
        rd.last_purchase_price as rd_price,
        rd.goodsQty as rd_qty,
        rd.goodsQtyIn as rd_qty_in,
        rd.goodsQtyInAmt as rd_qty_in_amt
    FROM dz_real_sales_bottomtable AS bt
    INNER JOIN dz_real_sales_detail AS rd ON bt.goods_no = rd.goods_no
    WHERE 
        (rd.last_purchase_price IS NOT NULL AND bt.last_purchase_price IS NOT NULL)
        OR (rd.goodsQty IS NOT NULL AND bt.goodsQty IS NOT NULL)
        OR (rd.goodsQtyIn IS NOT NULL AND bt.current_month_inventory IS NOT NULL)
        OR (rd.goodsQtyInAmt IS NOT NULL AND bt.current_month_inventory_amount IS NOT NULL)
    LIMIT 5
    """

    cursor.execute(verify_sql)
    results = cursor.fetchall()

    if results:
        logger.info("更新结果验证（前5条记录）：")
        for row in results:
            logger.info(f"货品编码: {row[0]}, 货品名称: {row[1]}")
            logger.info(f"  底表价格: {row[2]}, 明细表价格: {row[6]}")
            logger.info(f"  底表数量: {row[3]}, 明细表数量: {row[7]}")
            logger.info(f"  底表库存: {row[4]}, 明细表入库数量: {row[8]}")
            logger.info(f"  底表库存金额: {row[5]}, 明细表入库金额: {row[9]}")
            logger.info("---")


def get_update_statistics() -> Optional[Tuple[int, int]]:
    """
    获取更新统计信息
    
    Returns:
        Tuple[int, int]: (总记录数, 已更新记录数) 或 None（如果查询失败）
    """
    conn = None
    cursor = None

    try:
        conn = DB()
        cursor = conn.cursor()

        # 统计总记录数
        cursor.execute("SELECT COUNT(*) FROM dz_real_sales_bottomtable")
        total_count = cursor.fetchone()[0]

        # 统计已更新记录数（有价格、数量、库存或库存金额数据的记录）
        cursor.execute("""
        SELECT COUNT(*) FROM dz_real_sales_bottomtable 
        WHERE last_purchase_price IS NOT NULL 
           OR goodsQty IS NOT NULL
           OR current_month_inventory IS NOT NULL
           OR current_month_inventory_amount IS NOT NULL
        """)
        updated_count = cursor.fetchone()[0]

        return total_count, updated_count

    except Exception as e:
        logger.error(f"获取统计信息时发生错误: {str(e)}")
        return None

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# 新方法，功能是当dz_real_sales_detail 表中的字段更新到 dz_real_sales_bottomtable 表中时，发现dz_real_sales_detail不在dz_real_sales_bottomtable 中，则把该条数据更新进dz_real_sales_bottomtable
# 关联条件不变，dz_real_sales_bottomtable中的累计进货量、累计进货金额及为这条数据的当月进货量、当月进货金额

def insert_missing_records_to_bottomtable() -> bool:
    """
    将dz_real_sales_detail表中不存在于dz_real_sales_bottomtable表的记录插入到底表中
    
    功能说明：
    - 查找dz_real_sales_detail中存在但dz_real_sales_bottomtable中不存在的记录（基于goods_no关联）
    - 将这些记录插入到dz_real_sales_bottomtable中
    - 累计进货量、累计进货金额设置为当月进货量、当月进货金额
    
    Returns:
        bool: 插入成功返回True，失败返回False
    """
    logger.info("开始执行缺失记录插入程序")

    conn = None
    cursor = None

    try:
        # 建立数据库连接
        conn = DB()
        cursor = conn.cursor()
        logger.info("数据库连接成功")

        # 执行缺失记录插入
        success = _execute_missing_records_insert(cursor)

        if success:
            conn.commit()
            logger.info("缺失记录插入完成并提交事务")
            return True
        else:
            conn.rollback()
            logger.error("缺失记录插入失败，已回滚事务")
            return False

    except Exception as e:
        if conn:
            conn.rollback()
        logger.error(f"插入过程中发生异常: {str(e)}")
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        logger.info("数据库连接已关闭")


def _execute_missing_records_insert(cursor) -> bool:
    """
    执行缺失记录插入操作
    
    Args:
        cursor: 数据库游标对象
        
    Returns:
        bool: 插入成功返回True，失败返回False
    """
    try:
        # 1. 首先检查有多少条缺失记录
        missing_count = _check_missing_records(cursor)
        if missing_count == 0:
            logger.info("没有发现缺失的记录")
            return True

        logger.info(f"发现 {missing_count} 条缺失记录需要插入")

        # 2. 执行插入操作
        insert_sql = """
        INSERT INTO dz_real_sales_bottomtable (
            vendor_id,
            vendor_name,
            sku_id,
            goods_no,
            good_name,
            purchase_quantity,
            purchase_amount,
            current_month_inventory,
            current_month_inventory_amount,
            goodsQty,
            last_purchase_price,
            purchaser
        )
        SELECT 
            rd.vendor_id,
            rd.vend_name as vendor_name,
            rd.sku_id,
            rd.goods_no,
            rd.goods_name as good_name,
            CAST(COALESCE(rd.goodsQtyIn, 0) AS CHAR) as purchase_quantity,
            CAST(COALESCE(rd.goodsQtyInAmt, 0) AS CHAR) as purchase_amount,
            CAST(COALESCE(rd.goodsQtyIn, 0) AS CHAR) as current_month_inventory,
            CAST(COALESCE(rd.goodsQtyInAmt, 0) AS CHAR) as current_month_inventory_amount,
            CAST(COALESCE(rd.goodsQty, 0) AS CHAR) as goodsQty,
            CAST(COALESCE(rd.last_purchase_price, 0) AS CHAR) as last_purchase_price,
            '系统自动' as purchaser
        FROM dz_real_sales_detail rd
        LEFT JOIN dz_real_sales_bottomtable bt ON rd.goods_no = bt.goods_no
        WHERE bt.goods_no IS NULL
          AND rd.goods_no IS NOT NULL
          AND rd.goods_no != ''
        """

        cursor.execute(insert_sql)
        affected_rows = cursor.rowcount

        logger.info(f"成功插入 {affected_rows} 条缺失记录")

        # 3. 验证插入结果
        _verify_insert_result(cursor, affected_rows)

        return True

    except Exception as e:
        logger.error(f"执行缺失记录插入时发生错误: {str(e)}")
        return False


def _check_missing_records(cursor) -> int:
    """
    检查缺失记录数量
    
    Args:
        cursor: 数据库游标对象
        
    Returns:
        int: 缺失记录数量
    """
    check_sql = """
    SELECT COUNT(*) as missing_count
    FROM dz_real_sales_detail rd
    LEFT JOIN dz_real_sales_bottomtable bt ON rd.goods_no = bt.goods_no
    WHERE bt.goods_no IS NULL
      AND rd.goods_no IS NOT NULL
      AND rd.goods_no != ''
    """

    cursor.execute(check_sql)
    result = cursor.fetchone()
    return result[0] if result else 0


def _verify_insert_result(cursor, expected_count: int) -> None:
    """
    验证插入结果
    
    Args:
        cursor: 数据库游标对象
        expected_count: 期望插入的记录数
    """
    verify_sql = """
    SELECT 
        bt.goods_no,
        bt.good_name,
        bt.vendor_name,
        bt.purchase_quantity,
        bt.purchase_amount,
        bt.current_month_inventory,
        bt.current_month_inventory_amount,
        bt.goodsQty,
        bt.last_purchase_price,
        bt.purchaser
    FROM dz_real_sales_bottomtable bt
    WHERE bt.purchaser = '系统自动'
    ORDER BY bt.id DESC
    LIMIT 5
    """

    cursor.execute(verify_sql)
    results = cursor.fetchall()

    if results:
        logger.info("插入结果验证（最新5条记录）：")
        for row in results:
            logger.info(f"货品编码: {row[0]}, 货品名称: {row[1]}")
            logger.info(f"  供应商: {row[2]}, 采购员: {row[9]}")
            logger.info(f"  累计进货量: {row[3]}, 累计进货金额: {row[4]}")
            logger.info(f"  当月库存: {row[5]}, 当月库存金额: {row[6]}")
            logger.info(f"  当月销量: {row[7]}, 最近采购价: {row[8]}")
            logger.info("---")

    # 验证插入数量
    count_sql = "SELECT COUNT(*) FROM dz_real_sales_bottomtable WHERE purchaser = '系统自动'"
    cursor.execute(count_sql)
    actual_count = cursor.fetchone()[0]

    if actual_count >= expected_count:
        logger.info(f"插入验证成功：期望插入 {expected_count} 条，实际系统自动记录 {actual_count} 条")
    else:
        logger.warning(f"插入验证异常：期望插入 {expected_count} 条，实际系统自动记录 {actual_count} 条")


def update_bottomtable_with_missing_records() -> bool:
    """
    1.purchase_quantity = purchase_quantity + current_month_inventory
    2.purchase_amount = purchase_amount + current_month_inventory_amount
    以上是更新规则
    表是dz_real_sales_bottomtable
    
    Returns:
        bool: 全部操作成功返回True，任一操作失败返回False
    """
    logger.info("开始执行综合底表更新程序（字段累加更新）")
    conn = None
    cursor = None
    try:
        conn = DB()
        cursor = conn.cursor()
        logger.info("数据库连接成功")

        # 执行累加更新
        update_sql = """
        UPDATE dz_real_sales_bottomtable
        SET 
            purchase_quantity = CAST(COALESCE(purchase_quantity, 0) AS DECIMAL(20,4)) + CAST(COALESCE(current_month_inventory, 0) AS DECIMAL(20,4)),
            purchase_amount = CAST(COALESCE(purchase_amount, 0) AS DECIMAL(20,4)) + CAST(COALESCE(current_month_inventory_amount, 0) AS DECIMAL(20,4))
        """
        cursor.execute(update_sql)
        affected_rows = cursor.rowcount
        logger.info(f"成功累加更新 {affected_rows} 条记录")
        conn.commit()
        logger.info("累加更新已提交事务")
        return True
    except Exception as e:
        if conn:
            conn.rollback()
        logger.error(f"累加更新过程中发生异常: {str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        logger.info("数据库连接已关闭")


if __name__ == "__main__":
    import sys

    # 检查命令行参数
    if len(sys.argv) > 1:
        operation = sys.argv[1].lower()

        if operation == "insert":
            # 仅执行缺失记录插入
            print("执行缺失记录插入程序...")
            success = insert_missing_records_to_bottomtable()
            operation_name = "缺失记录插入"

        elif operation == "update":
            # 仅执行字段更新
            print("执行字段更新程序...")
            success = update_bottomtable_fields()
            operation_name = "字段更新"

        elif operation == "all":
            # 执行综合更新（字段更新 + 缺失记录插入）
            print("执行综合更新程序...")
            success = update_bottomtable_with_missing_records()
            operation_name = "综合更新"

        else:
            print("无效的操作参数！")
            print("使用方法：")
            print("  python updateBottomtableFields.py update    # 仅更新字段")
            print("  python updateBottomtableFields.py insert    # 仅插入缺失记录")
            print("  python updateBottomtableFields.py all       # 综合更新（推荐）")
            sys.exit(1)
    else:
        # 默认执行字段更新（保持向后兼容）
        print("执行默认字段更新程序...")
        success = update_bottomtable_fields()
        operation_name = "字段更新"

    # 显示执行结果
    if success:
        # 获取并显示统计信息
        stats = get_update_statistics()
        if stats:
            total, updated = stats
            logger.info(f"更新完成！总记录数: {total}, 已更新记录数: {updated}")
        print(f"{operation_name}程序执行成功！")
    else:
        print(f"{operation_name}程序执行失败！")
        sys.exit(1)
